package com.psy.price.recorder.manager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.psy.price.recorder.domain.Record;
import com.psy.price.recorder.util.DbUtil;
import com.psy.price.recorder.util.LogUtil;

public class RecordManager {

	private static RecordManager instance = new RecordManager();

	private RecordManager(){

	}

	public static RecordManager getinstance(){
		return instance;
	}


	public void addRecords (Record[] records){
		String sql = "INSERT INTO `psy`.`psy_price_recorder`(`ISBN13`,`SALES_RANK`,`DATE`)" +
                 "VALUES(?,?,?)";

		Connection conn = null;
		PreparedStatement pstmt = null;
		int key = 0;

		try {
			conn = DbUtil.getConnection();
			pstmt = conn.prepareStatement(sql, pstmt.RETURN_GENERATED_KEYS);

			for (int i = 0; i < records.length; i++) {

				pstmt.setString(1, records[i].getIsbn13());
				pstmt.setInt(2, records[i].getSalesRank());
				pstmt.setDate(3, records[i].getDate());
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			ResultSet keys = pstmt.getGeneratedKeys();
			int i =0;
			while (keys.next()) {

				key = keys.getInt(1);
				addAMZRecords(conn, key, records[i]);
				// addHalfRecords(conn, key, record);
				addBBKRecords(conn, key, records[i]);
				i++;
			}
			keys.close();

		} catch (SQLException e) {
			e.printStackTrace();
			LogUtil.getLogger().error(e.getMessage());
		} finally {
			DbUtil.close(pstmt);
			DbUtil.close(conn);

		}
	}


	public void addAMZRecords(Connection conn,int recordID,Record record){
		String sql = "INSERT INTO `psy`.`psy_amz`(`RECORD_ID`,`PRICE`,`QTY`,`SELLER_NAME`)" +
        "VALUES(?,?,?,?)";
		PreparedStatement pstmt = null;

		try {
			conn = DbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			int count = 0;
			if (null==record.getA()) {
				LogUtil.getLogger().error("AMAZON:"+record.getIsbn13()+" no value");
				return;
			}
			else if (record.getA().length<5) count = record.getA().length;
			else count = 5;
			for (int i = 0; i < count; i++) {
				pstmt.setInt(1, recordID);
				pstmt.setDouble(2, record.getA()[i].getOfferPrice());
				pstmt.setInt(3, record.getA()[i].getQuantity());
				pstmt.setString(4, record.getA()[i].getSellerName());
				//System.out.println(record.getA()[i].getSellerName());
				pstmt.addBatch();
			}
			pstmt.executeBatch();
		} catch (SQLException e) {
			e.printStackTrace();
			LogUtil.getLogger().error("RecordID: "+recordID+" "+e);
		} finally {
			DbUtil.close(pstmt);
			DbUtil.close(conn);
		}
	}



	public void addHalfRecords(Connection conn,int recordID,Record record){
		String sql = "INSERT INTO `psy`.`psy_half`(`RECORD_ID`,`PRICE`,`QTY`,`SELLER_NAME`)" +
        "VALUES(?,?,?,?)";
		PreparedStatement pstmt = null;

		try {
			conn = DbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			for (int i = 0; i < 2; i++) {
				pstmt.setInt(1, recordID);
				pstmt.setDouble(2, record.getH()[i].getOfferPrice());
				pstmt.setInt(3, record.getH()[i].getQty());
				pstmt.setString(4, record.getH()[i].getSellerName());
				pstmt.addBatch();
			}
			pstmt.executeBatch();
		} catch (SQLException e) {
			e.printStackTrace();
			LogUtil.getLogger().error("RecordID: "+recordID+" "+e.getMessage());
		} finally {
			DbUtil.close(pstmt);
			DbUtil.close(conn);
		}
	}

	public void addBBKRecords(Connection conn,int recordID,Record record){
		String sql = "INSERT INTO `psy`.`psy_bbk`(`RECORD_ID`,`PRICE`,`VENDOR_NAME`)" +
        "VALUES(?,?,?)";
		PreparedStatement pstmt = null;

		try {
			conn = DbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			int count = 0;
			if (null==record.getBB()) {
				LogUtil.getLogger().error("BBK:"+record.getIsbn13()+" no value");
				return;
			}
			else if (record.getBB().size()<5) count = record.getBB().size();
			else count = 5;

			for (int i = 0; i < count; i++) {
				pstmt.setInt(1, recordID);
				pstmt.setDouble(2, record.getBB().get(i).getPrice());
				pstmt.setString(3, record.getBB().get(i).getCompanyName());
				pstmt.addBatch();
			}
			pstmt.executeBatch();
		} catch (SQLException e) {
			e.printStackTrace();
			LogUtil.getLogger().error("RecordID: "+recordID+" "+e.getMessage());
		} finally {
			DbUtil.close(pstmt);
			DbUtil.close(conn);
		}
	}

}







